package com.anhry.app.safety.manage.oa.service.impl;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.anhry.app.safety.manage.enterprise.bean.TbCorpPerson;
import com.anhry.app.safety.manage.oa.bean.TbOaNotice;
import com.anhry.app.safety.manage.oa.bean.TbOaNoticeUser;
import com.anhry.app.safety.manage.oa.service.NoticeService;
import com.anhry.app.safety.manage.system.bean.TbUser;
import com.anhry.app.safety.util.dao.UtilDao;

/**
 * @author chen
 * @date 2012-8-13
 */
@Service
public class NoticeServiceImpl implements NoticeService {

	private static Logger logger = Logger.getLogger(NoticeServiceImpl.class);

	@Resource
	private UtilDao utilDao;

	public UtilDao getUtilDao() {
		return utilDao;
	}

	/**
	 * 已发通知列表
	 * 
	 * @return
	 */
	public List<TbOaNotice> loadNoticeList(TbUser user, String beginTime,
			String endTime, String keyWord, final int startRow, final int size)
			throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaNotice notice left join fetch notice.tbUser  left join fetch notice.tbUser.tbUserUnit   left join fetch notice.tbUser.tbUserDept  where notice.noticeTitle like '%"
						+ keyWord + "%' and notice.isDele=0");
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbOaNotice notice where notice.noticeTitle like '%"
							+ keyWord + "%' and notice.isDele=0");
		}
		if (null != beginTime && !"".equals(beginTime)) {
			sbf.append(" and to_char(notice.noticeTime,'yyyy-mm-dd') >= '"
					+ beginTime + "'");
		}
		if (null != endTime && !"".equals(endTime)) {
			sbf.append(" and to_char(notice.noticeTime,'yyyy-mm-dd') <= '"
				    + endTime + "'");
		}
		if (null != user) {
			sbf.append(" and notice.tbUser.id='" + user.getId() + "'");
		}
		sbf.append(" order by notice.noticeTime desc");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			if (size != 0) {
				query.setFirstResult(startRow);
				query.setMaxResults(size);
			}
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}
		
		return list;
	}

	public List<TbOaNotice> loadNoticeList(String beginTime, String endTime,
			String keyWord, String sendUser, String hql,
			String isNots, String isEs, final int startRow, final int size)
			throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaNotice notice left join fetch notice.tbUser  left join fetch notice.tbUser.tbUserUnit   left join fetch notice.tbUser.tbUserDept  where notice.noticeTitle like '%"
						+ keyWord + "%' and notice.isDele=0");
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbOaNotice notice where notice.noticeTitle like '%"
							+ keyWord + "%' and notice.isDele=0");
		}
		if (null != beginTime && !"".equals(beginTime)) {
			sbf.append(" and to_char(notice.noticeTime,'yyyy-mm-dd') >= '"
					+ beginTime +"'");
		}
		if (null != endTime	&& !"".equals(endTime)) {
			sbf.append(" and to_char(notice.noticeTime,'yyyy-mm-dd') <= '"
				   + endTime + "'");
		}
		if (null != sendUser && !"".equals(sendUser)) {
			sbf.append("  and notice.tbUser.userRealName like '%" + sendUser
					+ "%'");
		}
		if (null != isNots && !"".equals(isNots)) {
			sbf.append(" and notice.isNote='" + isNots + "'");
		}
		if (null != isEs && !"".equals(isEs)) {
			sbf.append(" and notice.isExigence='" + isEs + "'");
		}
		if (null != hql && !"".equals(hql)) {
			sbf.append(hql);
		}	
		sbf.append(" order by notice.noticeTime desc");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			if (size != 0) {
				query.setFirstResult(startRow);
				query.setMaxResults(size);
			}
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		
		return list;
	}

	/**
	 * 已收到通知
	 * 
	 * @return
	 */

	public List<TbOaNoticeUser> loadInNoticeList(TbUser user, String beginTime,
			String endTime, String keyWord, final int startRow, final int size)
			throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaNoticeUser notice left join fetch notice.tbUser left join fetch notice.tbOaNotice    left join fetch notice.tbOaNotice.tbUser.tbUserUnit " +
				" left join fetch notice.tbOaNotice.tbUser.tbUserDept    where notice.tbOaNotice.noticeTitle like '%"
						+ keyWord
						+ "%' and notice.isDele='0' and ( notice.tbOaNotice.isTop is null or notice.tbOaNotice.isTop = 0) and notice.tbUser.id="
						+ user.getId() + " and notice.userType='0'");
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbOaNoticeUser notice where notice.tbOaNotice.noticeTitle like '%"
							+ keyWord
							+ "%' and notice.isDele='0' and ( notice.tbOaNotice.isTop is null or notice.tbOaNotice.isTop = 0) and notice.tbUser.id="
							+ user.getId() + " and notice.userType='0'");
		}
		if (null != beginTime && !"".equals(beginTime)) {
			sbf
					.append(" and to_char(notice.tbOaNotice.noticeTime,'yyyy-mm-dd') >= '"
							+ beginTime + "'");
		}
		if (null != endTime	&& !"".equals(endTime)) {
			sbf
					.append(" and to_char(notice.tbOaNotice.noticeTime,'yyyy-mm-dd') <= '"
							 + endTime + "'");
		}
		sbf.append(" order by notice.tbOaNotice.noticeTime desc,notice.isRead");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			if (size != 0) {
				query.setFirstResult(startRow);
				query.setMaxResults(size);
			}
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		return list;
	}
	
	public List<TbOaNoticeUser> loadTopNoticeList(TbUser user, String beginTime,
			String endTime, String keyWord, final int startRow, final int size)
			throws Exception {
		if (null == keyWord) {
			keyWord = "";
		}
		StringBuffer sbf = new StringBuffer(
				"from TbOaNoticeUser notice left join fetch notice.tbUser left join fetch notice.tbOaNotice    left join fetch notice.tbOaNotice.tbUser.tbUserUnit " +
				" left join fetch notice.tbOaNotice.tbUser.tbUserDept    where notice.tbOaNotice.noticeTitle like '%"
						+ keyWord
						+ "%' and notice.isDele=0 and notice.tbOaNotice.isTop=1  and notice.tbUser.id='"
						+ user.getId() + "' and notice.userType=0");
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbOaNoticeUser notice where notice.tbOaNotice.noticeTitle like '%"
							+ keyWord
							+ "%' and notice.isDele=0 and notice.tbOaNotice.isTop=1 and notice.tbUser.id='"
							+ user.getId() + "' and notice.userType=0");
		}
		if (null != beginTime && !"".equals(beginTime)) {
			sbf
					.append(" and to_char(notice.tbOaNotice.noticeTime,'yyyy-mm-dd') >= '"
							+ beginTime + "'");
		}
		if (null != endTime	&& !"".equals(endTime)) {
			sbf
					.append(" and to_char(notice.tbOaNotice.noticeTime,'yyyy-mm-dd') <= '"
							 + endTime + "'");
		}
		sbf.append(" order by notice.tbOaNotice.noticeTime desc,notice.isRead");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			if (size != 0) {
				query.setFirstResult(startRow);
				query.setMaxResults(size);
			}
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		return list;
	}
	public List<TbOaNoticeUser> loadInNoticeList(TbUser user, String isRead, int size)
			throws Exception {
		StringBuffer sbf = new StringBuffer(
				"from TbOaNoticeUser notice left join fetch notice.tbUser left join fetch notice.tbOaNotice    left join fetch notice.tbOaNotice.tbUser.tbUserUnit   left join fetch notice.tbOaNotice.tbUser.tbUserDept    where notice.isRead="+isRead+" and notice.isDele=0 and notice.tbUser.id='"
						+ user.getId() + "' ");
		sbf.append(" order by notice.tbOaNotice.noticeTime desc");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			query.setFirstResult(0);
			query.setMaxResults(size);
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		return list;
	}
	
	/*
	 * 需短信通知的通知列表
	 */
	public List<TbOaNoticeUser> getNoteNoticeList() throws Exception {
		StringBuffer sbf = new StringBuffer(
				"from TbOaNoticeUser notice left join fetch notice.tbUser left join fetch notice.tbOaNotice    left join fetch notice.tbOaNotice.tbUser.tbUserUnit   left join fetch notice.tbOaNotice.tbUser.tbUserDept    where notice.tbOaNotice.isNote = 1 and notice.isDele=0");
		sbf.append(" and to_char(notice.tbOaNotice.noticeTime+notice.tbOaNotice.noteDay+notice.tbOaNotice.noteHour/24+notice.tbOaNotice.noteSec/24/60,'yyyy-MM-dd HH:mm') = to_char(sysdate,'yyyy-MM-dd HH:mm')");
		sbf.append(" order by notice.tbOaNotice.noticeTime asc");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List<TbOaNoticeUser> list = new ArrayList<TbOaNoticeUser>();
		try {
			Query query = null;
			query = s.createQuery(sql);
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}
		this.getPhone(list);
		return list;
		}

	//打印短信提醒信息和对应号码
	public void getPhone(List<TbOaNoticeUser> list) throws Exception{
		for(TbOaNoticeUser notice : list){
			System.out.println(notice.getTbOaNotice().getNoteContent());
			if(notice.getTbCorpInfo() != null){
				if("0".equals(notice.getTbOaNotice().getIsCorper())){
					if(((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "2").get(0)).getPerMobile()!=null &&
							((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "2").get(0)).getPerMobile().length()>0){
						System.out.println(((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "2").get(0)).getPerMobile());
					}else{
						logger.debug("NoticeServiceImpl：getPhone没填写手机号码");
						System.out.println("没填写完整信息");
					}
				}else if("1".equals(notice.getTbOaNotice().getIsCorper())){
					if(((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "3").get(0)).getPerMobile()!=null &&
							((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "3").get(0)).getPerMobile().length()>0){
						System.out.println(((TbCorpPerson)this.getCorpPerson(notice.getTbCorpInfo().getId(), "3").get(0)).getPerMobile());
					}else{
						logger.debug("NoticeServiceImpl：getPhone没填写手机号码");
						System.out.println("没填写完整信息");
					}
				}
			}
			if(notice.getTbUser() !=  null){
			    if(notice.getTbUser().getUserPhone() != null && notice.getTbUser().getUserPhone().length() != 0){
			    System.out.println(notice.getTbUser().getUserPhone());
			    }else{
			    	logger.debug("NoticeServiceImpl：getPhone没填写手机号码");
			    	System.out.println("没填写完整信息");
			    }
			}
		}
	}
	
	//根据企业ID和人员类型查找corpPerson
    public List getCorpPerson(Long corpId,String perType) throws Exception{
    	StringBuffer sbf = new StringBuffer();
		sbf = new StringBuffer("from TbCorpPerson where corpId = ");
		sbf.append(corpId);
		sbf.append(" and perType = '");
		sbf.append(perType);
		sbf.append("'");
	final String sql = sbf.toString();
	Session s = utilDao.getHibernateSession();
	List list = new ArrayList();
	try {
		Query query = null;
		query = s.createQuery(sql);
		list = query.list();
	}finally{
		if(s!=null&&s.isOpen()){
			s.close();
		}
	}	
	return  list;
}
   
	/**
	 * 查询未读 通知
	 */

	public int getNotReadNoticeSize(TbUser user)
			throws Exception {
		StringBuffer sbf = new StringBuffer();
			sbf = new StringBuffer(
					"select count(*) from TbOaNoticeUser notice where notice.isDele=0 and notice.tbUser.id='"
							+ user.getId() + "' and notice.isRead=0 and notice.tbOaNotice.isTop!=1");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		return  Integer.parseInt(list.get(0).toString());
	}
	
	public int getTopNotReadNoticeSize(TbUser user)
	throws Exception {
		StringBuffer sbf = new StringBuffer();
			sbf = new StringBuffer(
					"select count(*) from TbOaNoticeUser notice  where notice.isDele=0 and notice.tbUser.id='"
							+ user.getId() + "' and notice.isRead=0 and notice.tbOaNotice.isTop=1");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		return  Integer.parseInt(list.get(0).toString());
		}
	
	
	/**
	 * 查看对方阅读状态列表
	 * 
	 * @return
	 */
	public List<TbOaNoticeUser> loadNoticeReadList(Long id, String readType,
			final int startRow, final int size) throws Exception {
		StringBuffer sbf = new StringBuffer(
				"from TbOaNoticeUser notice left join fetch notice.tbUser left join fetch notice.tbCorpInfo  left join fetch notice.tbCorpInfo.tbCounty left join fetch notice.tbCorpInfo.tbStreet left join fetch notice.tbCorpInfo.tbVillage  left join fetch notice.tbOaNotice   left join fetch notice.tbUser.tbUserDept  left join fetch notice.tbUser.tbUserUnit   where notice.tbOaNotice.id='"
						+ id + "'");
		//left join fetch notice.tbCorpInfo.tbUserUnit
		if (size == 0) {
			sbf = new StringBuffer(
					"select count(*) from TbOaNoticeUser notice where notice.tbOaNotice.id='"
							+ id + "'");
		}
		if (null != readType && !"".equals(readType)) {
			sbf.append(" and notice.isRead='" + readType + "'");
		}
		sbf.append(" order by notice.id");
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			if (size != 0) {
				query.setFirstResult(startRow);
				query.setMaxResults(size);
			}
			list = query.list();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		
		return list;
	}
	
	public void setIsRead(Long id) throws Exception {
		StringBuffer sbf = new StringBuffer(
				"update  TbOaNoticeUser   notice  set notice.isRead=0   where notice.tbOaNotice.id='"
						+ id + "'");
		//left join fetch notice.tbCorpInfo.tbUserUnit
		final String sql = sbf.toString();
		Session s = utilDao.getHibernateSession();
		List list = new ArrayList();
		try {
			Query query = null;
			query = s.createQuery(sql);
			query.executeUpdate();
		}finally{
			if(s!=null&&s.isOpen()){
				s.close();
			}
		}	
		
	}

}
